﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
 using MySql.Data.MySqlClient;

namespace HRAssistant
{
    public partial class SalaryForm : Form
    {
        public SalaryForm()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string connStr = ConfigurationManager.AppSettings["MySqlConnStr"];//;MySQL 数据库连接字符串;
            string sqlCommand = "";
            if (textBox1.Text.Trim() != "")
            {
              
                sqlCommand = "select d.employee_id ,CONCAT(  '', e.emp_lastname, e.emp_middle_name, e.emp_firstname ) name, d.hours,d.ebsal_basic_salary,d.salary from (SELECT b.employee_id, b.totaltime/60 hours, c.ebsal_basic_salary,(b.totaltime/60)*c.ebsal_basic_salary salary FROM (SELECT employee_id, SUM( worktime ) totaltime FROM (SELECT employee_id, punch_in_user_time, punch_out_user_time, TIMESTAMPDIFF( MINUTE , punch_in_user_time, punch_out_user_time ) worktime, state FROM ohrm_attendance_record where  employee_id=" + Convert.ToInt32(textBox1.Text.Trim()) + " and punch_in_user_time between '" + dateTimePicker1.Value + "' and '" + dateTimePicker2.Value + "') AS a GROUP BY employee_id) AS b LEFT JOIN hs_hr_emp_basicsalary c ON b.employee_id = c.emp_number) as d left join hs_hr_employee e on d.employee_id =e.emp_number";
            }
            else
            {
                sqlCommand = "select d.employee_id ,CONCAT(  '', e.emp_lastname, e.emp_middle_name, e.emp_firstname ) name, d.hours,d.ebsal_basic_salary,d.salary from (SELECT b.employee_id, b.totaltime/60 hours, c.ebsal_basic_salary,(b.totaltime/60)*c.ebsal_basic_salary salary FROM (SELECT employee_id, SUM( worktime ) totaltime FROM (SELECT employee_id, punch_in_user_time, punch_out_user_time, TIMESTAMPDIFF( MINUTE , punch_in_user_time, punch_out_user_time ) worktime, state FROM ohrm_attendance_record where punch_in_user_time between '"+dateTimePicker1.Value+"' and '"+dateTimePicker2.Value+"') AS a GROUP BY employee_id) AS b LEFT JOIN hs_hr_emp_basicsalary c ON b.employee_id = c.emp_number) as d left join hs_hr_employee e on d.employee_id =e.emp_number";
              
            }

            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                conn.Open();
            }
            catch
            {
                throw;
            }

            try
            {

                MySqlDataAdapter myadp = new MySqlDataAdapter(sqlCommand, conn);
                DataSet ds = new DataSet();
                myadp.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];
                MessageBox.Show("本次查询完毕!");
            }
            catch
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
            //if (textBox1.Text.Trim() == string.Empty)
            //{
            //    dataGridView1.DataSource = HRAssistant.Data.ExecuteSP.OperateSP("sp_salary_all", true, dateTimePicker1, dateTimePicker2, null).Tables[0];
            //}
            //else
            //{
            //    dataGridView1.DataSource = HRAssistant.Data.ExecuteSP.OperateSP("sp_salary", false, dateTimePicker1, dateTimePicker2, textBox1).Tables[0];
            //}
            //dataGridView1.DataSource = HRAssistant.Data.ExecuteSP.OperateSP("sp_salary_all",true,dateTimePicker1,dateTimePicker2,textBox1).Tables[1];
        }

        private void RightClick_MouseDown(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                contextMenuStrip1.Show(dataGridView1, e.Location);
            }
        }

        private void 导出到excelToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Dictionary<string, string> dic = new Dictionary<string, string>();
            dic.Add("employee_id", "工号");
            dic.Add("name", "姓名");
            dic.Add("hours", "累计时间（小时）");
            dic.Add("ebsal_basic_salary", "工资基数");
            dic.Add("salary", "累计工资");
            
            HRForm.ReadWriteExcel.ExportTasks(dataGridView1, dic);
        }


    }
}
